package com.itbz.mapper;

import com.itbz.pojo.Users;
import com.itbz.util.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * 动态条件查询测试类
 */
public class DynamicConditionQueryTest {
  /**
   * 动态条件查询Users
   */
  public List<Users> queryUsers(Users users){
    List<Users> list= new ArrayList<>();
    Connection conn =null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try{
      //获取数据库连接
      conn = JdbcUtils.getConnection();
      //拼接查询SQL语句
      String sql = this.generateSql(users);
      System.out.println(sql);
      //创建PreparedStatement对象
      ps = conn.prepareStatement(sql);
      //执行sql语句
      rs = ps.executeQuery();
      while(rs.next()){
        Users user = new Users();
        user.setUserid(rs.getInt("userid"));
        user.setUsername(rs.getString("username"));
        user.setUserage(rs.getInt("userage"));
        list.add(user);
       }
     }catch(Exception e){
      e.printStackTrace();
     }finally{
      JdbcUtils.closeResource(rs,ps,conn);
     }
    return list;
   }


  /**
   * 生成动态条件查询sql
   */
  private String generateSql(Users users){
    StringBuffer sb = new StringBuffer("select * from users where 1=1 ");
    if(users.getUserid() > 0){
      sb.append(" and userid = ").append(users.getUserid());
     }
    if(users.getUsername() !=null &&users.getUsername().length() > 0){
      sb.append(" and username = '").append(users.getUsername()).append("'");
     }
    if(users.getUserage() > 0){
      sb.append(" and userage = ").append(users.getUserage());
     }
    return sb.toString();
   }


  public static void main(String[] args) {
    DynamicConditionQueryTest dt = new DynamicConditionQueryTest();
    Users users = new Users();
    users.setUsername("Oldlu");
    users.setUserage(20);
    List<Users> list = dt.queryUsers(users);
    for(Users user1:list){
      System.out.println(user1.getUserid()+" "+user1.getUsername()+" "+user1.getUserage());
     }
   }
}
